﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace DAL.Service.Impl
{
    public class BookServiceImpl : BaseServiceImpl, IBookService
    {
        public IList userGetCurrentList(int uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select lb_id,lb_bookName,lbi_time,lb_author,lbi_state,lb_aboutEbook,lb_aboutMp3 FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id =b.lbid_lbiid  INNER JOIN joy.LibraryBookDetail e ON b.lbid_lbdid=e.lbd_id INNER JOIN joy.LibraryBook d ON e.lbd_lbid = d.lb_id WHERE a.lbi_state>=@stateS AND a.lbi_state<@stateE AND lbi_uid=@uid order by  lbi_time Desc;");
            query.addParas("@stateS", Enums.bookstate.处理中);
            query.addParas("@stateE", Enums.bookstate.已归还);
            query.addParas("@uid", uid);
            return base.getList(query);
        }
        public IList userGetHistroyList(int pageno, int pagesize,int uid)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            if (pageno == 1)
            {
                sb.Append("SELECT count(lbi_id) as total  FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id =b.lbid_lbiid  INNER JOIN joy.LibraryBookDetail e ON b.lbid_lbdid=e.lbd_id INNER JOIN joy.LibraryBook d ON e.lbd_lbid = d.lb_id WHERE a.lbi_state = @state AND lbi_uid=@uid;");
                sb.Append("select top " + pagesize + " lb_id,lb_bookName,lbi_time,lb_author,lbi_state FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id =b.lbid_lbiid  INNER JOIN joy.LibraryBookDetail e ON b.lbid_lbdid=e.lbd_id INNER JOIN joy.LibraryBook d ON e.lbd_lbid = d.lb_id WHERE a.lbi_state = @state AND lbi_uid=@uid order by  lbi_time Desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select lb_id,lb_bookName,lbi_time,lb_author,lbi_state,ROW_NUMBER() OVER (order by lbi_time desc) as rno FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id =b.lbid_lbiid  INNER JOIN joy.LibraryBookDetail e ON b.lbid_lbdid=e.lbd_id INNER JOIN joy.LibraryBook d ON e.lbd_lbid = d.lb_id WHERE a.lbi_state = @state AND lbi_uid=@uid");
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by lbi_time desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            query.addParas("@state", Enums.bookstate.已归还);
            query.addParas("@uid", uid);
            return base.getList(query);
        }
        public IList userGetRemind(int uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT lb_bookName,lbi_time,dateadd(day,7,a.lbi_time) return_time FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id = b.lbid_lbiid INNER JOIN joy.LibraryBookDetail e ON b.lbid_lbdid=e.lbd_lbid INNER JOIN joy.LibraryBook d ON e.lbd_lbid = d.lb_id WHERE a.lbi_state = @state AND lbi_uid=@uid AND  datediff(day,a.lbi_time,getdate()) >6 order by  lbi_time asc");
            query.addParas("@state", Enums.bookstate.阅读中);
            query.addParas("@uid", uid);
            return base.getList(query);
        }
        public IList getExpireRemind()
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT lbi_id,u_name,u_tel1,lbd_isbn,lb_bookName FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id = b.lbid_lbiid INNER JOIN joy.UserInfo c ON a.lbi_uid = c.u_id  INNER JOIN joy.LibraryBookDetail e ON b.lbid_lbdid=e.lbd_lbid INNER JOIN joy.LibraryBook d ON e.lbd_lbid = d.lb_id WHERE a.lbi_state = @state AND  datediff(day,a.lbi_time,getdate()) >7");
            query.addParas("@state", Enums.bookstate.阅读中);
            return base.getList(query);
        }
        public IList getReturnRemind()
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT lbi_id,u_name,u_tel1,lbd_isbn,lb_bookName FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id = b.lbid_lbiid INNER JOIN joy.UserInfo c ON a.lbi_uid = c.u_id  INNER JOIN joy.LibraryBookDetail e ON b.lbid_lbdid=e.lbd_lbid INNER JOIN joy.LibraryBook d ON e.lbd_lbid = d.lb_id WHERE a.lbi_state = @state AND  datediff(day,a.lbi_time,getdate()) between 6 AND 7");
            query.addParas("@state", Enums.bookstate.阅读中);
            return base.getList(query);
        }
        public IList getReturnBooks(string bookname, string in_s, string in_e, int pageno, int pagesize)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1=1";
            if (!String.IsNullOrEmpty(bookname))
            {
                _sql += " and lb_bookName like '%" + bookname + "%'";
            }

            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and iol_time > @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and iol_time < @in_e";
                query.addParas("@in_e", in_e);
            }
            _sql += " and iol_nowState = @state";
            query.addParas("@state", Enums.bookstate.已归还);
            if (pageno == 1)
            {
                sb.Append("SELECT count(iol_id) as total FROM joy.IndentOperationLog " + _sql + ";");
                sb.Append("select top " + pagesize + " u_name,u_tel1,u_address,lb_bookName,lb_author  FROM joy.IndentOperationLog a INNER JOIN joy.LibraryBookIndent b ON a.iol_iblid = b.lbi_id INNER JOIN joy.UserInfo c ON b.lbi_uid = c.u_id INNER JOIN joy.LibraryBook d ON b.lbi_lbid = d.lb_id " + _sql + " order by iol_id desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select u_name,u_tel1,u_address,lb_bookName,lb_author,ROW_NUMBER() OVER (order by iol_id desc) as rno FROM joy.IndentOperationLog a INNER JOIN joy.LibraryBookIndent b ON a.iol_iblid = b.lbi_id INNER JOIN joy.UserInfo c ON b.lbi_uid = c.u_id INNER JOIN joy.LibraryBook d ON b.lbi_lbid = d.lb_id " + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by iol_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }
        public IList getloanBooks(string bookname, string in_s, string in_e, int pageno, int pagesize)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1=1";
            if (!String.IsNullOrEmpty(bookname))
            {
                _sql += " and lb_bookName like '%" + bookname + "%'";
            }

            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and iol_time > @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and iol_time < @in_e";
                query.addParas("@in_e", in_e);
            }
            _sql += " and iol_nowState = @state";
            query.addParas("@state", Enums.bookstate.处理中);
            if (pageno == 1)
            {
                sb.Append("SELECT count(iol_id) as total FROM joy.IndentOperationLog " + _sql + ";");
                sb.Append("select top " + pagesize + " u_name,u_tel1,u_address,lb_bookName,lb_author,lbi_state  FROM joy.IndentOperationLog a INNER JOIN joy.LibraryBookIndent b ON a.iol_iblid = b.lbi_id INNER JOIN joy.UserInfo c ON b.lbi_uid = c.u_id INNER JOIN joy.LibraryBook d ON b.lbi_lbid = d.lb_id " + _sql + " order by iol_id desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select u_name,u_tel1,u_address,lb_bookName,lb_author,lbi_state,ROW_NUMBER() OVER (order by iol_id desc) as rno FROM joy.IndentOperationLog a INNER JOIN joy.LibraryBookIndent b ON a.iol_iblid = b.lbi_id INNER JOIN joy.UserInfo c ON b.lbi_uid = c.u_id INNER JOIN joy.LibraryBook d ON b.lbi_lbid = d.lb_id " + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by iol_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }

        public IList getputInStorage(string bookname, string in_s, string in_e, int pageno, int pagesize)
        {

            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1=1";
            if (!String.IsNullOrEmpty(bookname))
            {
                _sql += " and lb_bookName like '%" + bookname + "%'";
            }

            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and lb_addTime > @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and lb_addTime < @in_e";
                query.addParas("@in_e", in_e);
            }
            if (pageno == 1)
            {
                sb.Append("SELECT count(lb_id) as total FROM joy.LibraryBook " + _sql + ";");
                sb.Append("select top " + pagesize + " lb_bookName,lb_author,lb_press,lb_price  FROM joy.LibraryBook " + _sql + " order by lb_id desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select lb_bookName,lb_author,lb_press,lb_price,ROW_NUMBER() OVER (order by lb_id desc) as rno FROM joy.LibraryBook " + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by lb_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }

        public MyQuery getLibraryBookistMyQuery(string bookName, string translatedName, int language, int ebook,
            int forAge, int level, int bookType, int bookTheme, string author, string press,
            string in_s, string in_e, int state, int pageno, int pagesize, string isbn, string selbookaboutcd, string txtbookcount, string txtbooksum, string txtbsum, string txtwhere, string txtjiage, string olb_bookName, string olb_ebook, string olb_forAge, string olb_level, string olb_bookType, string olb_bookTheme, string olb_booksCount, string olb_inventory, string olb_position, string olb_price, string olb_state, string olb_id,int flag)
        {

            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1 = 1 ";
            string _order = "";
            #region order
            if (olb_bookName == "true")
            {
                _order += "lb_bookName desc,";
            }
            if (olb_ebook == "true")
            {
                _order += " lb_ebook desc,";
            }
            if (olb_forAge == "true")
            {
                _order += " lb_forAge desc,";
            }
            if (olb_level == "true")
            {
                _order += " lb_level desc,";
            }
            if (olb_bookType == "true")
            {
                _order += " lb_bookType desc,";
            }
            if (olb_bookTheme == "true")
            {
                _order += " lb_bookTheme desc,";
            }

            if (olb_booksCount == "true")
            {
                _order += " lb_booksCount desc,";
            }
            if (olb_inventory == "true")
            {
                _order += " lb_inventory desc,";
            }
            if (olb_position == "true")
            {
                _order += " lb_position desc,";
            }
            if (olb_price == "true")
            {
                _order += " lb_price desc,";
            }
            if (olb_state == "true")
            {
                _order += " lb_state desc,";
            }
            if (olb_id == "true" || _order == "")
            {
                _order += " lb_id desc,";
            }

            _order = _order.Substring(0, _order.Length - 1);
            #endregion

            if (!String.IsNullOrEmpty(bookName))
            {
                _sql += " and lb_bookName like '%" + bookName + "%'";
            }
            if (!String.IsNullOrEmpty(translatedName))
            {
                _sql += " and lb_translatedName like '%" + translatedName + "%'";
            }
            if (language > -1)
            {
                _sql += " and lb_language =" + language;
            }
            if (ebook > -1)
            {
                _sql += " and lb_ebook =" + ebook;
            }
            if (forAge > -1)
            {
                _sql += " and lb_forAge =" + forAge;
            }
            if (level > -1)
            {
                _sql += " and lb_level =" + level;
            }
            if (bookType > -1)
            {
                _sql += " and lb_bookType =" + bookType;
            }
            if (bookTheme > -1)
            {
                _sql += " and lb_bookTheme =" + bookTheme;
            }
            if (state > -1)
            {
                _sql += " and lb_state =" + state;
            }
            if (!String.IsNullOrEmpty(author))
            {
                _sql += " and lb_author like '%" + author + "%'";
            }
            if (!String.IsNullOrEmpty(press))
            {
                _sql += " and lb_press like '%" + press + "%'";
            }            
            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and lb_addTime >= @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and lb_addTime < @in_e";
                query.addParas("@in_e", in_e);
            }
            if (!String.IsNullOrEmpty(isbn))
            {
                _sql += " and lb_id in(select distinct lbd_lbid from joy.LibraryBookDetail where lbd_isbn like '%" + isbn + "%') ";
            }
            if (int.Parse(selbookaboutcd) > -1)
            {
                _sql += " and lb_aboutCd =" + selbookaboutcd;//, string txtbooksum, string txtbsum, string txtwhere, string txtjiage
            }
            if (!String.IsNullOrEmpty(txtbookcount))
            {
                _sql += " and lb_booksCount = " + txtbookcount;
            }
            if (!String.IsNullOrEmpty(txtbooksum))
            {
                _sql += " and lb_inventory = " + txtbooksum;
            }
            if (!String.IsNullOrEmpty(txtbsum))
            {
                _sql += " and (lb_booksCount-lb_inventory) = " + txtbsum;
            }
            if (!String.IsNullOrEmpty(txtwhere))
            {
                _sql += " and lb_position like '%" + txtwhere + "%'";
            }
            if (!String.IsNullOrEmpty(txtjiage))
            {
                _sql += " and lb_price = " + txtjiage;
            }
            if (pageno == 1)
            {
                if (flag == 1)
                { 
                    sb.Append("SELECT count(lb_id) as total FROM joy.LibraryBook " + _sql + ";");
                }
                
                sb.Append("select top " + pagesize + " *,(lb_booksCount-lb_inventory) bbooksum  FROM joy.LibraryBook " + _sql + " order by  " + _order + ";");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select *,ROW_NUMBER() OVER (order by  " + _order + ") as rno,(lb_booksCount-lb_inventory) bbooksum FROM joy.LibraryBook " + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by  " + _order + ";");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return query;
        }
        public IList getLibraryBookist(string bookName, string translatedName, int language, int ebook,
    int forAge, int level, int bookType, int bookTheme, string author, string press,
    string in_s, string in_e, int state, int pageno, int pagesize, string isbn, string selbookaboutcd, string txtbookcount, string txtbooksum, string txtbsum, string txtwhere, string txtjiage, string olb_bookName, string olb_ebook, string olb_forAge, string olb_level, string olb_bookType, string olb_bookTheme, string olb_booksCount, string olb_inventory, string olb_position, string olb_price, string olb_state, string olb_id)
        {
            return base.getList(getLibraryBookistMyQuery( bookName,  translatedName,  language,  ebook,
     forAge,  level,  bookType,  bookTheme,  author,  press,
     in_s,  in_e,  state,  pageno,  pagesize,  isbn,  selbookaboutcd,  txtbookcount,  txtbooksum,  txtbsum,  txtwhere,  txtjiage,  olb_bookName,  olb_ebook,  olb_forAge,  olb_level,  olb_bookType,  olb_bookTheme,  olb_booksCount,  olb_inventory,  olb_position,  olb_price,  olb_state,  olb_id,1));
        }

        public System.Data.DataTable getLibraryBookistDatatable(string bookName, string translatedName, int language, int ebook,
int forAge, int level, int bookType, int bookTheme, string author, string press,
string in_s, string in_e, int state, int pageno, int pagesize, string isbn, string selbookaboutcd, string txtbookcount, string txtbooksum, string txtbsum, string txtwhere, string txtjiage, string olb_bookName, string olb_ebook, string olb_forAge, string olb_level, string olb_bookType, string olb_bookTheme, string olb_booksCount, string olb_inventory, string olb_position, string olb_price, string olb_state, string olb_id)
        {
            return base.getDataTable(getLibraryBookistMyQuery(bookName, translatedName, language, ebook,
     forAge, level, bookType, bookTheme, author, press,
     in_s, in_e, state, pageno, pagesize, isbn, selbookaboutcd, txtbookcount, txtbooksum, txtbsum, txtwhere, txtjiage, olb_bookName, olb_ebook, olb_forAge, olb_level, olb_bookType, olb_bookTheme, olb_booksCount, olb_inventory, olb_position, olb_price, olb_state, olb_id,0));
        }
        public System.Data.DataTable getBookDetaillistDatatable(string bookName, string translatedName, int language, int ebook,
int forAge, int level, int bookType, int bookTheme, string author, string press,
string in_s, string in_e, int state, int pageno, int pagesize, string isbn, string selbookaboutcd, string txtwhere, string txtjiage, string olb_bookName, string olb_ebook, string olb_forAge, string olb_level, string olb_bookType, string olb_bookTheme, string olb_state, string olb_id, string olbd_isbn, string olb_translatedName, string olb_language, string jc_s, string jc_e, string gh_s, string gh_e, string selmp3)
        {
            return base.getDataTable(bookdetaillistMyQuery(bookName, translatedName, language, ebook,
     forAge, level, bookType, bookTheme, author, press,
     in_s, in_e, state, pageno, pagesize, isbn, selbookaboutcd, txtwhere, txtjiage, olb_bookName, olb_ebook, olb_forAge, olb_level, olb_bookType, olb_bookTheme, olb_state, olb_id, 0, olbd_isbn, olb_translatedName, olb_language, jc_s, jc_e, gh_s, gh_e, selmp3));
        }
        public IList getBookDetaillist(string bookName, string translatedName, int language, int ebook,
int forAge, int level, int bookType, int bookTheme, string author, string press,
string in_s, string in_e, int state, int pageno, int pagesize, string isbn, string selbookaboutcd, string txtwhere, string txtjiage, string olb_bookName, string olb_ebook, string olb_forAge, string olb_level, string olb_bookType, string olb_bookTheme, string olb_state, string olb_id, string olbd_isbn, string olb_translatedName, string olb_language, string jc_s, string jc_e, string gh_s, string gh_e, string selmp3)
        {
            return base.getList(bookdetaillistMyQuery(bookName, translatedName, language, ebook,
     forAge, level, bookType, bookTheme, author, press,
     in_s, in_e, state, pageno, pagesize, isbn, selbookaboutcd, txtwhere, txtjiage, olb_bookName, olb_ebook, olb_forAge, olb_level, olb_bookType, olb_bookTheme, olb_state, olb_id, 1, olbd_isbn, olb_translatedName, olb_language, jc_s, jc_e, gh_s, gh_e, selmp3));
        }

        public MyQuery bookdetaillistMyQuery(string bookName, string translatedName, int language, int ebook,
    int forAge, int level, int bookType, int bookTheme, string author, string press,
    string in_s, string in_e, int state, int pageno, int pagesize, string isbn, string selbookaboutcd, string txtwhere, string txtjiage, string olb_bookName, string olb_ebook, string olb_forAge, string olb_level, string olb_bookType, string olb_bookTheme, string olb_state, string olb_id, int flag, string olbd_isbn, string olb_translatedName, string olb_language, string jc_s, string jc_e, string gh_s, string gh_e, string selmp3)
        {

            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1 = 1 ";
            string _order = "";
            #region order
            if (olbd_isbn == "true")
            {
                _order += "lbd_isbn desc,";
            }
            if (olb_bookName == "true")
            {
                _order += "lb_bookName desc,";
            }
            if (olb_translatedName == "true")
            {
                _order += "lb_translatedName desc,";
            }
            if (olb_language == "true")
            {
                _order += "lb_language desc,";
            }
            if (olb_ebook == "true")
            {
                _order += " lb_ebook desc,";
            }
            if (olb_forAge == "true")
            {
                _order += " lb_forAge desc,";
            }
            if (olb_level == "true")
            {
                _order += " lb_level desc,";
            }
            if (olb_bookType == "true")
            {
                _order += " lb_bookType desc,";
            }
            if (olb_bookTheme == "true")
            {
                _order += " lb_bookTheme desc,";
            }
            if (olb_state == "true")
            {
                _order += " lbd_state desc,";
            }
            if (olb_id == "true" || _order == "")
            {
                _order += " lbd_id desc,";
            }

            _order = _order.Substring(0, _order.Length - 1);
            #endregion

            if (!String.IsNullOrEmpty(bookName))
            {
                _sql += " and lb_bookName like '%" + bookName + "%'";
            }
            if (!String.IsNullOrEmpty(translatedName))
            {
                _sql += " and lb_translatedName like '%" + translatedName + "%'";
            }
            if (language > -1)
            {
                _sql += " and lb_language =" + language;
            }
            if (ebook > -1)
            {
                _sql += " and lb_ebook =" + ebook;
            }
            if (forAge > -1)
            {
                _sql += " and lb_forAge =" + forAge;
            }
            if (level > -1)
            {
                _sql += " and lb_level =" + level;
            }
            if (bookType > -1)
            {
                _sql += " and lb_bookType =" + bookType;
            }
            if (bookTheme > -1)
            {
                _sql += " and lb_bookTheme =" + bookTheme;
            }
            if (state > -1)
            {
                _sql += " and lbd_state =" + state;
            }
            if (!String.IsNullOrEmpty(author))
            {
                _sql += " and lb_author like '%" + author + "%'";
            }
            if (!String.IsNullOrEmpty(press))
            {
                _sql += " and lb_press like '%" + press + "%'";
            }
            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and lbd_addTime >= @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and lbd_addTime < @in_e";
                query.addParas("@in_e", in_e);
            }
            if (!String.IsNullOrEmpty(jc_s))
            {
                _sql += " and times >= @jc_s";
                query.addParas("@jc_s", jc_s);
            }
            if (!String.IsNullOrEmpty(jc_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and times < @jc_e";
                query.addParas("@jc_e", jc_e);
            }
            if (!String.IsNullOrEmpty(gh_s))
            {
                _sql += " and timee >= @gh_s";
                query.addParas("@gh_s", gh_s);
            }
            if (!String.IsNullOrEmpty(gh_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and timee < @gh_e";
                query.addParas("@gh_e", gh_e);
            }
            if (!String.IsNullOrEmpty(isbn))
            {
                _sql += " and lb_id in(select distinct lbd_lbid from joy.LibraryBookDetail where lbd_isbn like '%" + isbn + "%') ";
            }
            if (int.Parse(selbookaboutcd) > -1)
            {
                _sql += " and lb_aboutCd =" + selbookaboutcd; 
            }
            if (int.Parse(selmp3) ==1)
            {
                _sql += " and isnull(lb_aboutMp3,'') !='' ";
            }
            else if (int.Parse(selmp3) == 0)
            {
                _sql += " and isnull(lb_aboutMp3,'') =='' ";
            }
            if (!String.IsNullOrEmpty(txtwhere))
            {
                _sql += " and lb_position like '%" + txtwhere + "%'";
            }
            if (!String.IsNullOrEmpty(txtjiage))
            {
                _sql += " and lb_price = " + txtjiage;
            }
            if (pageno == 1)
            {
                if (flag == 1)
                {
                    sb.Append("SELECT count(lb_id) as total FROM (select joy.getBorrowBookTimee(lbd_id) timee,joy.getBorrowBookTimes(lbd_id) times,* from joy.LibraryBook a inner join joy.LibraryBookDetail b on a.lb_id =b.lbd_lbid) a " + _sql + ";");
                }

                sb.Append("select top " + pagesize + " * FROM (select joy.getBorrowBookTimee(lbd_id) timee,joy.getBorrowBookTimes(lbd_id) times,* from joy.LibraryBook a inner join joy.LibraryBookDetail b on a.lb_id =b.lbd_lbid) a " + _sql + " order by  " + _order + ";");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select *,ROW_NUMBER() OVER (order by  " + _order + ") as rno FROM (select joy.getBorrowBookTimee(lbd_id) timee,joy.getBorrowBookTimes(lbd_id) times,* from joy.LibraryBook a inner join joy.LibraryBookDetail b on a.lb_id =b.lbd_lbid) b " + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by  " + _order + ";");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return query;
        }
        public IList getLibraryBookbyid(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT * FROM joy.LibraryBook WHERE lb_id=@id");
            query.addParas("@id", id);
            return base.getList(query);
        }

        public int deleteLibraryBook(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("DELETE FROM joy.LibraryBook WHERE lb_id =@id");
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }
        public IList getisbnborrowlist(int lbdid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select lbd_id,joy.getBorrowBookTimee(lbd_id) timee,joy.getBorrowBookTimes(lbd_id) times,joy.getcard(lbi_ucid) ucard,joy.getuserinfo(lbi_uid,'u_name') uname,joy.getuserinfo(lbi_uid,'u_eName') uename  from joy.LibraryBook a inner join joy.LibraryBookDetail b on a.lb_id=b.lbd_lbid inner join joy.LibraryBookIndentDetail c on b.lbd_id =c.lbid_lbdid inner join joy.LibraryBookIndent d on c.lbid_lbiid = d.lbi_id where lbid_lbdid=@lbdid");
            query.addParas("@lbdid", lbdid);
            return base.getList(query);
        }
        public IList getbookIsbnList(string lbid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT *,joy.getAdminName(isnull(lbd_lastUser,lbd_user)) adminname,isnull(lbd_updateTime,lbd_addTime) lasttime FROM joy.LibraryBookDetail WHERE lbd_lbid=@lbid order by lbd_id desc");
            query.addParas("@lbid", lbid);
            return base.getList(query);
        }
        public int updateBookState(int id, int state)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("UPDATE joy.LibraryBook SET lb_state=@state WHERE lb_id =@id");
            query.addParas("@state", state);
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }

        public int updateIsbnBookState(int id, int state)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("UPDATE joy.LibraryBookDetail SET lbd_state=@state WHERE lbd_id =@id");
            query.addParas("@state", state);
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }

        public int addInventOry(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("UPDATE  joy.LibraryBook SET lb_inventory=lb_inventory+1 WHERE lb_id =@id");
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }

        public int subtractInventOry(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("UPDATE  joy.LibraryBook SET lb_inventory=lb_inventory-1 WHERE lb_id =@id");
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }

        public void booksCountJs(string lbid, int user)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("update joy.LibraryBook set lb_inventory=(select count(lbd_id) from joy.LibraryBookDetail where lbd_state=0 and lbd_lbid=lb_id),lb_booksCount=(select count(lbd_id) from joy.LibraryBookDetail where (lbd_state=0 or lbd_state=1) and lbd_lbid=lb_id),lb_lastUser=@user,lb_updateTime=getdate() where lb_id =@lbid ");
            query.addParas("@lbid", lbid);
            query.addParas("@user", user);
            base.exeUpdateSql(query);
        }

        public IList getBooklist(string name, string author, string isbn)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = "";
            if (!String.IsNullOrEmpty(name))
            {
                _sql += " and lb_bookName like '%" + name + "%'";
            }
            if (!String.IsNullOrEmpty(author))
            {
                _sql += " and lb_author like '%" + author + "%'";
            }
            if (!String.IsNullOrEmpty(isbn))
            {
                _sql += " and lbd_isbn like '%" + isbn + "%'";
            }
            sb.Append(@"SELECT * FROM joy.LibraryBookDetail a inner join  joy.LibraryBook as b
                        on a.lbd_lbid=b.lb_id
                        WHERE lbd_id in (SELECT max(lbd_id) lbd_id FROM joy.LibraryBookDetail a WHERE lbd_state=0 Group by lbd_lbid) " + _sql + " order by lb_id desc;");
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }
        public IList saveRanking()
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select top 5 lb_id,lb_bookName,lb_cover from joy.LibraryBook a left join (select count(us_lbid) c ,us_lbid from joy.UserSave group by us_lbid) b on a.lb_id = b.us_lbid order by c desc");
            return base.getList(query);
        }
        public IList jyRanking()
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select top 5 lb_id,lb_bookName,lb_cover,joy.getBookBorrowCount(lb_id) c  from joy.LibraryBook order by c desc");
            return base.getList(query);
        }

        public IList getUserNameByBookId(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString(@"
                                select top 36 isnull(u_patriarchName1,isnull(u_patriarchName2,isnull(u_name,u_nick))) as uname from joy.LibraryBookIndentDetail as a 
                                inner join joy.LibraryBookDetail as b on  a.lbid_lbdid=b.lbd_id
                                inner join joy.LibraryBookIndent as c on a.lbid_lbiid =c.lbi_id
                                inner join joy.UserInfo d on c.lbi_uid=d.u_id where 
                                lbd_lbid=@id order by lbi_time desc");
            query.addParas("@id", id);
            return this.getList(query);
        }

        public IList getappraisal(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString(@"select isnull(u_patriarchName1,isnull(u_patriarchName2,isnull(u_name,u_nick))) as uname,lbi_appraisal from joy.LibraryBookIndentDetail as a 
                                inner join joy.LibraryBookDetail as b on  a.lbid_lbdid=b.lbd_id
                                inner join joy.LibraryBookIndent as c on a.lbid_lbiid =c.lbi_id
                                inner join joy.UserInfo d on c.lbi_uid=d.u_id where 
                                lbd_lbid=@id and lbi_appraisal is not null order by lbi_time desc");
            query.addParas("@id", id);
            return this.getList(query);
        }


        public int isbnCount(string isbn)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select count(lbd_id) c from joy.LibraryBookdetail where lbd_isbn=@isbn");
            query.addParas("@isbn", isbn);
            IList li = this.getList(query);
            return int.Parse(((Hashtable)li[0])["c"].ToString());
        }

        public IList getBooklist(string text, int language, int ebook,
            int forAge, int level, int bookType, int bookTheme, int cd, int mp3, int inventory, int pageno, int pagesize)
        {

            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1 = 1 ";
            if (language > -1)
            {
                _sql += " and lb_language =" + language;
            }
            if (ebook > -1)
            {
                _sql += " and lb_ebook =" + ebook;
            }
            if (forAge > -1)
            {
                _sql += " and lb_forAge =" + forAge;
            }
            if (level > -1)
            {
                _sql += " and lb_level =" + level;
            }
            if (bookType > -1)
            {
                _sql += " and lb_bookType =" + bookType;
            }
            if (bookTheme > -1)
            {
                _sql += " and lb_bookTheme =" + bookTheme;
            }
            if (cd > -1)
            {
                _sql += " and lb_aboutCd is not null ";
            }
            if (mp3 > -1)
            {
                _sql += " and lb_aboutMp3 is not null";
            }
            if (inventory > -1)
            {
                _sql += " and lb_inventory >0";
            }
            string sql = " where 1=1 and lb_state<>" + (int)DAL.Enums.bookState.已删除;
            if (text.Trim() != "")//关键字
            {
                text = text.ToString().TrimEnd();//去首空格符支持 例输入" 关键字"查询等同"关键字"
                text = text.ToString().TrimStart();//去尾空格符
                string[] ckeywordsplit;
                if (text.ToString().IndexOf(" ") > 0)//是否有多关键字查询
                {
                    ckeywordsplit = text.ToString().Split(' ');
                    for (int i = 0; i < ckeywordsplit.Count(); i++)
                    {
                        sql += " AND searchtext LIKE '%'+@" + i.ToString() + "+'%'";
                        query.addParas("@" + i.ToString(), ckeywordsplit[i].ToString());
                    }
                }
                else
                {
                    sql += " AND searchtext LIKE '%'+@C_KEYWORD+'%'";
                    query.addParas("@C_KEYWORD", text);
                }
            }
            if (pageno == 1)
            {
                sb.Append("SELECT count(lb_id) as total FROM(select *,lb_bookName+isnull(lb_translatedName,'')+lb_author+lb_press as searchtext from joy.LibraryBook " + _sql + ") c " + sql + ";");
                sb.Append("select top " + pagesize + " * from(select  *,lb_bookName+isnull(lb_translatedName,'')+lb_author+lb_press as searchtext  FROM joy.LibraryBook " + _sql + ") c" + sql + " order by lb_id desc;");
            }
            else
            {
                sb.Append("select * from(select * from(");
                sb.Append("select *,lb_bookName+isnull(lb_translatedName,'')+lb_author+lb_press as searchtext,ROW_NUMBER() OVER (order by lb_id desc) as rno FROM joy.LibraryBook " + _sql + ")c " + sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by lb_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }

        public int updateBookDetailState(int bookid, string isbn, int state)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("update joy.LibraryBookDetail set lbd_state=@state where lbd_lbid=@bookid and lbd_isbn=@isbn");
            query.addParas("@state", state);
            query.addParas("@bookid", bookid);
            query.addParas("@isbn", isbn);
            return base.exeUpdateSql(query);
        }

        public IList getBookIdIsbn(string bookname)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select top 1 lb_id,lbd_isbn,lbd_id from joy.LibraryBook as a inner join joy.LibraryBookDetail as b on a.lb_id=b.lbd_lbid where lb_bookName like '%" + bookname + "%'  order by lbd_id desc");
            //query.addParas("@bname", bookname);
            return base.getList(query);
        }

        public IList getBookDetailId(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select lbid_lbdid from  joy.LibraryBookIndentDetail where lbid_lbiid=@id");
            query.addParas("@id", id);
            return base.getList(query);
        }

        public IList getLibraryBookId(int detailId)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select lbd_lbid from  joy.LibraryBookDetail where lbd_id=@id");
            query.addParas("@id", detailId);
            return base.getList(query);
        }

        
    }

}
